package com.ai.view;

import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.ai.service.system.model.GkinfoModel;
import com.ai.service.system.model.HwlxinfoModel;
import com.ai.service.system.model.XxinfoModel;
import com.ai.service.system.model.ZdinfoModel;

/**
 * 导出特色航线Excel视图
 * 
 * @author Andy
 *
 */
public class SldataViewExcel extends AbstractExcelView {

	@SuppressWarnings("unchecked")
	@Override
	protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		String excelName = "铁水联运申报信息核准表.xls";
		// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
		response.setCharacterEncoding("utf-8");
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF-8"));

		// 产生Excel表头
		HSSFSheet sheet = workbook.createSheet("箱量核对模版");
		HSSFSheet hiddenzd = workbook.createSheet("hiddenzd");
		HSSFSheet hiddengk = workbook.createSheet("hiddengk");
		
		//标题样式 start
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		HSSFFont titlefont = workbook.createFont();
		titlefont.setFontHeightInPoints((short) 22);
		titleStyle.setFont(titlefont);
		//标题样式 end
		
		//描述样式 start
		HSSFCellStyle depictStyle = workbook.createCellStyle();
		depictStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		depictStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		HSSFFont depictFont = workbook.createFont();
		depictFont.setFontHeightInPoints((short) 10);
		depictStyle.setFont(depictFont);
		//描述样式 end

		//表头 start
		HSSFCellStyle headStyle = workbook.createCellStyle();
		headStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
		headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

		HSSFFont headFont = workbook.createFont();
		headFont.setFontHeightInPoints((short) 11);
		headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		headStyle.setFont(headFont);
		//表头样式 end
		
		HSSFRow title = sheet.createRow(0);
		title.setHeight((short) 500);
		HSSFCell hsstitle = title.createCell(0);
		hsstitle.setCellValue("铁水联运申报信息核准表");
		hsstitle.setCellStyle(titleStyle);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 20));
		
		HSSFRow depict = sheet.createRow(1);
		depict.setHeight((short) 500);
		HSSFCell hssdepict = depict.createCell(0);
		hssdepict.setCellValue("备注：申报企业应按实际运输情况，如实填写本表。同时将与本表信息所对应的铁路货票、水路运单以及相关证明文件盖章扫描后，按时提交至申报核查系统待查存档。核查人员将按要求进行信息抽检，如发现虚假数据将取消该企业补贴资格。");
		hssdepict.setCellStyle(depictStyle);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 20));

		HSSFRow head = sheet.createRow(2);
		head.setHeight((short) 400);
		
		HSSFCell h_xl = head.createCell(0);
		h_xl.setCellValue("序号");
		h_xl.setCellStyle(headStyle);
		
		HSSFCell h_yslx = head.createCell(1);
		h_yslx.setCellValue("运输类型");
		h_yslx.setCellStyle(headStyle);
		
		HSSFCell h_mylx = head.createCell(2);
		h_mylx.setCellValue("内贸/外贸");
		h_mylx.setCellStyle(headStyle);
		sheet.setColumnWidth(1, 5000);

		HSSFCell h_xh = head.createCell(3);
		h_xh.setCellValue("箱号");
		h_xh.setCellStyle(headStyle);
		
		HSSFCell h_hwlx = head.createCell(4);
		h_hwlx.setCellValue("货物类型");
		h_hwlx.setCellStyle(headStyle);
		sheet.setColumnWidth(3, 5000);

//		HSSFCell h_hwmc = head.createCell(5);
//		h_hwmc.setCellValue("货物名称");
//		h_hwmc.setCellStyle(headStyle);

		HSSFCell h_xx = head.createCell(5);
		h_xx.setCellValue("箱型");
		h_xx.setCellStyle(headStyle);
		sheet.setColumnWidth(5, 5000);

		HSSFCell h_crtlsj = head.createCell(6);
		h_crtlsj.setCellValue("出/入铁路站场时间");
		h_crtlsj.setCellStyle(headStyle);
		sheet.setColumnWidth(6, 5000);

		HSSFCell h_tlhph = head.createCell(7);
		h_tlhph.setCellValue("铁路货票号");
		h_tlhph.setCellStyle(headStyle);
		sheet.setColumnWidth(7, 5000);

		HSSFCell h_fz = head.createCell(8);
		h_fz.setCellValue("发站");
		h_fz.setCellStyle(headStyle);
		sheet.setColumnWidth(8, 5000);

		HSSFCell h_dz = head.createCell(9);
		h_dz.setCellValue("到站");
		h_dz.setCellStyle(headStyle);
		sheet.setColumnWidth(9, 5000);

		HSSFCell h_crgksj = head.createCell(10);
		h_crgksj.setCellValue("出/入港口时间");
		h_crgksj.setCellStyle(headStyle);
		sheet.setColumnWidth(10, 5000);

		HSSFCell h_hwydh = head.createCell(11);
		h_hwydh.setCellValue("货物运单号");
		h_hwydh.setCellStyle(headStyle);
		sheet.setColumnWidth(11, 5000);

//		HSSFCell h_fh = head.createCell(13);
//		h_fh.setCellValue("封号");
//		h_fh.setCellStyle(headStyle);
		
		HSSFCell h_cm = head.createCell(12);
		h_cm.setCellValue("船名");
		h_cm.setCellStyle(headStyle);
		
		HSSFCell h_hc = head.createCell(13);
		h_hc.setCellValue("航次");
		h_hc.setCellStyle(headStyle);
		
		HSSFCell h_mmsi = head.createCell(14);
		h_mmsi.setCellValue("MMSI号");
		h_mmsi.setCellStyle(headStyle);
		
		HSSFCell h_cbdg = head.createCell(15);
		h_cbdg.setCellValue("船舶到港");
		h_cbdg.setCellStyle(headStyle);
		sheet.setColumnWidth(16, 5000);
		
		HSSFCell h_cblg = head.createCell(16);
		h_cblg.setCellValue("船舶离港");
		h_cblg.setCellStyle(headStyle);
		sheet.setColumnWidth(17, 5000);
		
		HSSFCell h_bz = head.createCell(17);
		h_bz.setCellValue("备注");
		h_bz.setCellStyle(headStyle);
		
//		HSSFCell h_hgsbdh = head.createCell(18);
//		h_hgsbdh.setCellValue("海关申报单号");
//		h_hgsbdh.setCellStyle(headStyle);
		
		HSSFCell h_tlzpmc = head.createCell(18);
		h_tlzpmc.setCellValue("铁路照片名称");
		h_tlzpmc.setCellStyle(headStyle);
		sheet.setColumnWidth(18, 5000);
		
		HSSFCell h_slzpmc = head.createCell(19);
		h_slzpmc.setCellValue("水路照片名称");
		h_slzpmc.setCellStyle(headStyle);
		sheet.setColumnWidth(19, 5000);
		
		HSSFCell h_hgzpmc = head.createCell(20);
		h_hgzpmc.setCellValue("海关照片名称");
		h_hgzpmc.setCellStyle(headStyle);
		sheet.setColumnWidth(20, 5000);

		List<GkinfoModel> gklist = (List<GkinfoModel>) model.get("gklist");
		List<XxinfoModel> xxlist = (List<XxinfoModel>) model.get("xxlist");
		List<ZdinfoModel> zdlist = (List<ZdinfoModel>) model.get("zdlist");
		List<HwlxinfoModel> hwlxlist = (List<HwlxinfoModel>) model.get("hwlxlist");

		String[] list_gk = null;
		if (gklist.size()>0) {
			list_gk = new String[gklist.size()];
			for (int i = 0; i < gklist.size(); i++) {
				list_gk[i] = gklist.get(i).getGkmc();
			}
		}
		
		String[] list_xx = null;
		if (xxlist.size()>0) {
			list_xx = new String[xxlist.size()];
			for (int i = 0; i < xxlist.size(); i++) {
				list_xx[i] = xxlist.get(i).getXxmc();
			}
		}
		
		String[] list_zd = null;
		if (zdlist.size()>0) {
			list_zd = new String[zdlist.size()];
			for (int i = 0; i < zdlist.size(); i++) {
				list_zd[i] = zdlist.get(i).getZdmc();
			}
		}
		
		String[] list_hwlx = null;
		if (hwlxlist.size()>0) {
			list_hwlx = new String[hwlxlist.size()];
			for (int i = 0; i < hwlxlist.size(); i++) {
				list_hwlx[i] = hwlxlist.get(i).getHwlxmc();
			}
		}
		
		String[] list_yslx = new String[]{"铁水","水铁"};
		
		String[] list_mylx = new String[]{"内贸","外贸"};
		
		//创建隐藏列
		for (int i = 0, length= list_gk.length; i < length; i++) { 
		    String name = list_gk[i]; 
		    HSSFRow row = hiddengk.createRow(i); 
		    HSSFCell cell = row.createCell(15); 
		    cell.setCellValue(name);
		    HSSFCell cell18 = row.createCell(16); 
		    cell18.setCellValue(name);
		}
		for (int i = 0, length= list_zd.length; i < length; i++) { 
		    String name = list_zd[i]; 
		    HSSFRow row = hiddenzd.createRow(i); 
		    HSSFCell cell = row.createCell(8); 
		    cell.setCellValue(name);
		    HSSFCell cell10 = row.createCell(9); 
		    cell10.setCellValue(name);
		}

		HSSFRow row = sheet.createRow(3);
		HSSFDataFormat format = workbook.createDataFormat();
		HSSFCell r_yslx = row.createCell(1);
		r_yslx.setCellValue("请选择");
		DataValidation dv_yslx = getDataValidationByFormula(list_yslx, 3, 1);
		dv_yslx.createErrorBox("选择错误提示", "请选择运输类型");
		sheet.addValidationData(dv_yslx);
		
		HSSFCell r_mylx = row.createCell(2);
		r_mylx.setCellValue("请选择");
		DataValidation dv_mylx = getDataValidationByFormula(list_mylx, 3, 2);
		dv_mylx.createErrorBox("选择错误提示", "请选择贸易类型");
		sheet.addValidationData(dv_mylx);
		
		HSSFCell r_hwlx = row.createCell(4);
		r_hwlx.setCellValue("请选择");
		DataValidation dv_hwlx = getDataValidationByFormula(list_hwlx, 3, 4);
		dv_hwlx.createErrorBox("选择错误提示", "请选择货物类型");
		sheet.addValidationData(dv_hwlx);
		
		HSSFCell r_xx = row.createCell(5);
		r_xx.setCellValue("请选择");
		DataValidation dv_xx = getDataValidationByFormula(list_xx, 3, 5);
		dv_xx.createErrorBox("选择错误提示", "请选择箱型");
		sheet.addValidationData(dv_xx);

		HSSFCell r_fz = row.createCell(8);
		r_fz.setCellValue("请选择");
		//港口关联隐藏列
		Name namedCell = workbook.createName(); 
		namedCell.setNameName("hiddenzd"); 
		namedCell.setRefersToFormula("hiddenzd!A$1:A$" + list_zd.length); 
		DVConstraint constraint = DVConstraint.createFormulaListConstraint("hiddenzd"); 
		CellRangeAddressList fzaddressList = new CellRangeAddressList(3, 3, 8, 8); 
		HSSFDataValidation fzvalidation = new HSSFDataValidation(fzaddressList, constraint); 
		workbook.setSheetHidden(1, true); 
		sheet.addValidationData(fzvalidation);

		HSSFCell r_dz = row.createCell(9);
		r_dz.setCellValue("请选择");
		CellRangeAddressList dzaddressList = new CellRangeAddressList(3, 3, 9, 9); 
		HSSFDataValidation dzvalidation = new HSSFDataValidation(dzaddressList, constraint); 
		sheet.addValidationData(dzvalidation);
		
		HSSFCell r_cbdg = row.createCell(15);
		r_cbdg.setCellValue("请选择");
		Name namedCellgk = workbook.createName(); 
		namedCellgk.setNameName("hiddengk"); 
		namedCellgk.setRefersToFormula("hiddengk!A$1:A$" + list_gk.length); 
		DVConstraint constraintgk = DVConstraint.createFormulaListConstraint("hiddengk"); 
		CellRangeAddressList sfgddressList = new CellRangeAddressList(3, 3, 15, 15); 
		HSSFDataValidation sfgalidation = new HSSFDataValidation(sfgddressList, constraintgk); 
		workbook.setSheetHidden(2, true); 
		sheet.addValidationData(sfgalidation);

		HSSFCell r_cblg = row.createCell(16);
		r_cblg.setCellValue("请选择");
		CellRangeAddressList mdgddressList = new CellRangeAddressList(3, 3, 16, 16); 
		HSSFDataValidation mdgalidation = new HSSFDataValidation(mdgddressList, constraintgk); 
		sheet.addValidationData(mdgalidation);

		HSSFCellStyle datetimeStyle = workbook.createCellStyle();
		datetimeStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
		HSSFCell r_tlsj = row.createCell(7);
		r_tlsj.setCellStyle(datetimeStyle);

		HSSFCell r_slsj = row.createCell(11);
		r_slsj.setCellStyle(datetimeStyle);
	}

	private DataValidation getDataValidationByFormula(String[] formulaString, int naturalRowIndex,
			int naturalColumnIndex) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint.createExplicitListConstraint(formulaString);
		// 设置数据有效性加载在哪个单元格上。
		// 四个参数分别是：起始行、终止行、起始列、终止列
		int firstRow = naturalRowIndex;
		int lastRow = naturalRowIndex;
		int firstCol = naturalColumnIndex;
		int lastCol = naturalColumnIndex;
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		// 数据有效性对象
		DataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
		return data_validation_list;
	}
}
